/**
 * Copyright 2013-2015 JueYue (qrb.jueyue@gmail.com)
 *   
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package cn.afterturn.easypoi.excel;

import cn.afterturn.easypoi.entity.ExcelPojo;
import cn.afterturn.easypoi.excel.entity.ExportParams;
import cn.afterturn.easypoi.excel.entity.ImportParams;
import cn.afterturn.easypoi.excel.entity.result.ExcelImportResult;
import cn.afterturn.easypoi.excel.imports.ExcelImportService;
import cn.afterturn.easypoi.excel.imports.sax.SaxReadExcel;
import cn.afterturn.easypoi.exception.excel.ExcelImportException;
import cn.afterturn.easypoi.handler.inter.IReadHandler;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.util.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.UUID;

/**
 * Excel 导入工具
 * 
 * @author JueYue
 *  2013-9-24
 * @version 1.0
 */
@SuppressWarnings({ "unchecked" })
public class ExcelImportUtil {

    public static void main(String[] args) throws Exception {
        String path1 = "c:/a.xlsx";
        String path2 = "c:/b.xlsx";
        File file1 = new File(path1);
        File file2 = new File(path2);

        ImportParams params = new ImportParams();
        params.setTitleRows(0);
        params.setKeyIndex(0);
        ClassLoader classLoader = ExcelImportUtil.importExcel(file1, ExcelPojo.class, params).getClass().getClassLoader();
        System.out.println(classLoader);

        List<ExcelPojo> EP1 = ExcelImportUtil.importExcel(file1, ExcelPojo.class, params);
        List<ExcelPojo> EP2 = ExcelImportUtil.importExcel(file2, ExcelPojo.class, params);
        List<ExcelPojo> EP3 = new ArrayList<ExcelPojo>();

        //按条件筛选数据
        for (int i = 0; i < EP1.size(); i++) {
            ExcelPojo excelPojo = EP1.get(i);
            //得到第一张表中的第一个日期
            String date = excelPojo.getDate();
            //拿这个日期去第二张表中找相同日期的 (遍历第二张表)
            for (int j = 0; j < EP2.size(); j++) {
                //如果日期一样,再去比较店铺名
                if (date.equals(EP2.get(j).getDate())){
                    //如果店铺名一样,再去比较 单量是否相等
                    if (excelPojo.getName().equals(EP2.get(j).getName())){
                        //比较单量是否相等
                        if (excelPojo.getNum()==EP2.get(j).getNum()){
                            //比较总本金是否相等
                            if (excelPojo.getMoney()==EP2.get(j).getMoney()){
                                //如果以上条件都满足,那么把这个符合要求的数据存入List中
                                EP3.add(excelPojo);
                            }
                        }
                    }


                }
            }

        }

        //生成表
        ExportParams exportParams = new ExportParams();
        exportParams.setTitle("c");
        Workbook workbook = ExcelExportUtil.exportExcel(exportParams, ExcelPojo.class, EP3);
        FileOutputStream fos = new FileOutputStream("c:/"+ UUID.randomUUID()+".xls");
        workbook.write(fos);

    }


    private ExcelImportUtil() {
    }

    private static final Logger LOGGER = LoggerFactory.getLogger(ExcelImportUtil.class);

    /**
     * Excel 导入 数据源本地文件,不返回校验结果 导入 字 段类型 Integer,Long,Double,Date,String,Boolean
     * 
     * @param file
     * @param pojoClass
     * @param params
     * @return
     */
    public static <T> List<T> importExcel(File file, Class<?> pojoClass, ImportParams params) {
        FileInputStream in = null;
        try {
            in = new FileInputStream(file);
            return new ExcelImportService().importExcelByIs(in, pojoClass, params, false).getList();
        } catch (ExcelImportException e) {
            throw new ExcelImportException(e.getType(), e);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ExcelImportException(e.getMessage(), e);
        } finally {
            IOUtils.closeQuietly(in);
        }
    }

    /**
     * Excel 导入 数据源IO流,不返回校验结果 导入 字段类型 Integer,Long,Double,Date,String,Boolean
     * 
     * @param inputstream
     * @param pojoClass
     * @param params
     * @return
     * @throws Exception
     */
    public static <T> List<T> importExcel(InputStream inputstream, Class<?> pojoClass,
                                          ImportParams params) throws Exception {
        return new ExcelImportService().importExcelByIs(inputstream, pojoClass, params, false).getList();
    }

    /**
     * Excel 导入 数据源IO流 字段类型 Integer,Long,Double,Date,String,Boolean
     * 支持校验,支持Key-Value
     * 
     * @param inputstream
     * @param pojoClass
     * @param params
     * @return
     * @throws Exception
     */
    public static <T> ExcelImportResult<T> importExcelMore(InputStream inputstream,
                                                             Class<?> pojoClass,
                                                             ImportParams params) throws Exception {
        return new ExcelImportService().importExcelByIs(inputstream, pojoClass, params, true);
    }

    /**
     * Excel 导入 数据源本地文件 字段类型 Integer,Long,Double,Date,String,Boolean
     * 支持校验,支持Key-Value
     * @param file
     * @param pojoClass
     * @param params
     * @return
     */
    public static <T> ExcelImportResult<T> importExcelMore(File file, Class<?> pojoClass,
                                                             ImportParams params) {
        FileInputStream in = null;
        try {
            in = new FileInputStream(file);
            return new ExcelImportService().importExcelByIs(in, pojoClass, params, true);
        } catch (ExcelImportException e) {
            throw new ExcelImportException(e.getType(), e);
        } catch (Exception e) {
            LOGGER.error(e.getMessage(), e);
            throw new ExcelImportException(e.getMessage(), e);
        } finally {
            IOUtils.closeQuietly(in);
        }
    }

    /**
     * Excel 通过SAX解析方法,适合大数据导入,不支持图片
     * 导入 数据源本地文件,不返回校验结果 导入 字 段类型 Integer,Long,Double,Date,String,Boolean
     * 
     * @param inputstream
     * @param pojoClass
     * @param params
     * @param handler
     */
    public static void importExcelBySax(InputStream inputstream, Class<?> pojoClass,
                                        ImportParams params, IReadHandler handler) {
        new SaxReadExcel().readExcel(inputstream, pojoClass, params, handler);
    }

}
